package com.ruoyi.generator.service.sqlexec;


import cn.hutool.core.io.resource.ClassPathResource;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.redis.RedisCache;
import com.ruoyi.common.exception.file.FileSizeLimitExceededException;
import com.zgl.youshuda.core.utils.CommonUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;

/**
 * 运行sql文件服务类
 */
@Slf4j
@Service
public class ExeDbSqlService
{
    @Autowired
    private Map<String, DataSource> dataSources;

    @Autowired
    private RedisCache redisCache;

    //缓存在redis中的前缀
    private String cachePrefix = "sql_task:";

    private ExecutorService executorService = Executors.newFixedThreadPool(3);

    /**
     * 根据指定的Sql文件重置数据库
     * @return
     */
    public AjaxResult exeSqlFile(MultipartFile file){
        return null;
//        // 验证文件类型
//        if (!isSqlFile(file)) {
//            return AjaxResult.error( "仅支持 SQL 文件 (.sql)");
//        }
////        long size = file.getSize();
////        if (size > DEFAULT_MAX_SIZE)
////        {
////            throw new FileSizeLimitExceededException(DEFAULT_MAX_SIZE / 1024 / 1024);
////        }
//        SqlExecutionRecord record = createRecord(file);
//
//        boolean transactional = true;
//
//        executorService.submit(() -> {
//            try (Connection connection = getMasterDataSource().getConnection()) {
//                connection.setAutoCommit(!transactional);
//
//                // 更新状态为处理中
//                updateRecordStatus(record.getTarget(), "处理中", 0,0);
//
//                // 解析和执行 SQL
//                executeSqlContent(connection, file, record, transactional);
//
//                // 提交事务
//                if (transactional) {
//                    connection.commit();
//                }
//                // 更新状态为完成
//                updateRecordStatus(record.getTarget(), "已完成", -1,-1);
//            } catch (Exception e) {
//                // 更新状态为完成
//                updateRecordStatus(record.getTarget(), "失败", -1,-1);
//            }
//        });
//
//        return AjaxResult.success(record);
    }

    /**
     * 重置系统数据
     * @return
     */
    public AjaxResult resetSysData(){

        ClassPathResource resource = new ClassPathResource("sql/RESET.sql");

        SqlExecutionRecord record = createRecord(resource);

        boolean transactional = true;

        executorService.submit(() -> {
            try (Connection connection = getMasterDataSource().getConnection()) {
                connection.setAutoCommit(!transactional);

                // 更新状态为处理中
                updateRecordStatus(record.getTarget(), "处理中", 0,0);

                // 解析和执行 SQL
                executeSqlContent(connection, resource, record, transactional);

                // 提交事务
                if (transactional) {
                    connection.commit();
                }
                // 更新状态为完成
                updateRecordStatus(record.getTarget(), "已完成", -1,-1);
            } catch (Exception e) {
                // 更新状态为完成
                updateRecordStatus(record.getTarget(), "失败", -1,-1);
            }
        });

        return AjaxResult.success(record);
    }

    /**
     * 获取当前可用的数据源
     * @return
     */
    private DataSource getMasterDataSource(){
        DataSource currentDataSource = null;
        for(Map.Entry<String,DataSource> dataSource:dataSources.entrySet()){
            //记录数据源
            currentDataSource = dataSource.getValue();
            //masterDataSource
            if(String.valueOf(dataSource.getKey()).indexOf("master")>-1){
                //默认返回master数据源
                return currentDataSource;
            }
        }
        return currentDataSource;
    }

    /**
     * 验证是否为 SQL 文件
     */
    private boolean isSqlFile(MultipartFile file) {
        String fileName = file.getOriginalFilename();
        return fileName != null &&
                (fileName.endsWith(".sql") ||
                        file.getContentType() != null &&
                                file.getContentType().equals("application/sql"));
    }

    /**
     * 创建处理任务
     * @param file
     * @return
     */
    private SqlExecutionRecord createRecord(ClassPathResource file) {
        SqlExecutionRecord record = new SqlExecutionRecord();
        record.setTarget(CommonUtil.getUUID());
        record.setFileName(file.getName());
        record.setStatus("待处理");
        redisCache.setCacheObject(cachePrefix+record.getTarget(), record, 12, TimeUnit.HOURS);
        return record;
    }

    /**
     * 更新状态
     * @param target
     * @param status
     * @param successCount
     * @param errorCount
     */
    private void updateRecordStatus(String target, String status, int successCount,int errorCount) {
        SqlExecutionRecord record = redisCache.getCacheObject(cachePrefix+target);
        record.setStatus(status);
        if(successCount>-1){
            record.setSuccessCount(successCount);
        }
        if(errorCount>-1){
            record.setErrorCount(errorCount);
        }
        if(record.getStartTimeMillis() == null){
            record.setStartTimeMillis(System.currentTimeMillis());
            record.setUseTimeSeconds(0L);
        }else{
            record.setUseTimeSeconds((System.currentTimeMillis() - record.getStartTimeMillis()) / 1000);
        }
        redisCache.setCacheObject(cachePrefix+record.getTarget(), record, 12, TimeUnit.HOURS);
    }

    /**
     * 执行sql文件
     * @param connection
     * @param file
     * @param record
     * @param transactional
     */
    private void executeSqlContent(Connection connection, ClassPathResource file,
                                   SqlExecutionRecord record, boolean transactional)
            throws Exception {

        try (BufferedReader reader = new BufferedReader(
                new InputStreamReader(file.getStream(), StandardCharsets.UTF_8))) {

            StringBuilder currentStatement = new StringBuilder();
            int successCount = 0;
            int errorCount = 0;
            int statementCount = 0;
            String line;

            while ((line = reader.readLine()) != null) {
                // 跳过空行和注释
                if (line.trim().isEmpty() || line.trim().startsWith("--")) {
                    continue;
                }

                currentStatement.append(line).append("\n");

                // 检测语句结束
                if (line.trim().endsWith(";")) {
                    statementCount++;
                    String sql = currentStatement.toString().trim();

                    // 移除结尾的分号
                    if (sql.endsWith(";")) {
                        sql = sql.substring(0, sql.length() - 1);
                    }

                    try (Statement statement = connection.createStatement()) {
                        statement.execute(sql);

                        // 更新执行进度
                        if (statementCount % 10 == 0) {
                            updateRecordStatus(record.getTarget(), "处理中", successCount,errorCount);
                        }

                        successCount ++;

                    } catch (SQLException ex) {

                        log.error("语句："+sql+"执行异常，原因：",ex);
                        errorCount ++;

                        // 在事务模式下回滚
//                        if (transactional) {
//                            try {
//                                connection.rollback();
//                            } catch (SQLException rollbackEx) {
//                                // 记录回滚错误
//                            }
//                        }
//                        throw new Exception(
//                                "SQL执行错误: " + ex.getMessage() +
//                                        "\n在语句 #" + statementCount + ": " + sql, ex);
                    }

                    currentStatement.setLength(0); // 清空当前语句
                }
            }

            // 更新总语句数
            updateRecordStatus(record.getTarget(), "处理中", successCount,errorCount);
        }
    }

    /**
     * 获取执行进度
     */
    public SqlExecutionRecord getExecutionProgress(String target) {
        return redisCache.getCacheObject(cachePrefix+target);
    }

    /**
     * 删除执行进度
     */
    public boolean delExecutionTask(String target) {
        return redisCache.deleteObject(cachePrefix+target);
    }

}
